package batch.promionet;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.log4j.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.*;
import java.io.*;
import java.util.Date;
import java.util.Calendar;

public class promionetUserNoPhoneFile
{
    final String TODAY = DateFormatUtils.format(new Date(), "yyyyMMdd");
    final String FILEPATH = "/home01/sfc/batch/promionet/data/";

    private OutputStreamWriter oswForData;
    private int                j = 0;

    String strCountry = "";  //Which Country
    String strLang    = "";  //Which Language
    String strAddName = "No";  //Temp File Name
    String dataFileNm = "";

    public promionetUserNoPhoneFile(String strPrCountry, String strPrLang) {
        strCountry = StringUtils.defaultString(strPrCountry);
        strLang    = StringUtils.defaultString(strPrLang);
    }

    public static void main(String [] args) {
        Logger.getLogger("promionet.makeuserno").info("( * ) Start.");

        promionetUserNoPhoneFile ptUserNo = null;

        if( args.length == 2 ){
            ptUserNo = new promionetUserNoPhoneFile(args[0], args[1]);
        }
        else{
            System.exit(-1);
        }

        ptUserNo.startMake();

        Logger.getLogger("promionet.makeuserno").info("( * ) End.");
    }

    //Make a log file and a data file.
    private void startMake(){
        dataFileNm = "DataOfUserPhoneImage_" + TODAY;

        if(strCountry.length() > 0){
            dataFileNm += "_" + strCountry;
        }
        else{
            dataFileNm += "_all";
        }

        if(strLang.length() > 0){
            dataFileNm += "_" + strLang;
        }

        if(strAddName.length() > 0){
            dataFileNm += "_" + strAddName;
        }

        dataFileNm += ".txt";

        try {
            //To make Data File by 'UTF-8'
            oswForData = new OutputStreamWriter(new FileOutputStream(FILEPATH + dataFileNm, false), "UTF-8");

            getUsers();

        }
        catch(Exception ex) {
            ex.printStackTrace();
            Logger.getLogger("promionet.makeuserno").info(ex.toString());
        }
        finally {
            if( oswForData != null ) try { oswForData.close(); } catch(Exception ex) { ex.printStackTrace(); }
        }
    }

    //Obtain Users from DB.
    private void getUsers() throws Exception{
        Connection         conn  = null;
        PreparedStatement  pstmt = null;
        ResultSet          rs    = null;

        String  strID           = "";
        String  strFullSt       = "";

        StringBuffer strBuffer = new StringBuffer()
                                    .append("SELECT USER_NO , HP_MODEL                                                 \n")
                                    .append("  FROM G_MB_MEMBER                                                        \n")
                                    .append(" WHERE(LTRIM(RTRIM(EMAIL)) IS NOT NULL AND EMAIL <> 'WAP_TEMP')           \n")
                                    .append("   AND DELETED IS NULL                                                    \n")
                                    .append("   AND USER_NO IN (SELECT USER_NO FROM G_MB_OPTIN WHERE CODE = '0113001') \n")
                                    .append("   AND LANGUAGE    = '" + strLang + "'                                    \n")
                                    .append("   AND REG_COUNTRY = '" + strCountry + "'                                 \n");

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@eu-anydb:1521:ORAEU", "gsms", "any02gs");

            //Add Query
            strBuffer.append(this.addQuery());

            System.out.println("Query: " + strBuffer);

            pstmt = conn.prepareStatement(strBuffer.toString());
            rs = pstmt.executeQuery();

            while(rs.next()){
                strID = ObjectUtils.toString(rs.getObject(1), "");

                //Make Sentences
                strFullSt = strID + "\n";

                //Write down sentences on the file
                oswForData.write(strFullSt);

                j++;
            }

            Logger.getLogger("promionet.makeuserno").info("Total Count : [" + dataFileNm + "] - " + j);
        }
        finally {
            if(rs != null)    try { rs.close(); }    catch (Exception ex) { ex.printStackTrace(); }
            if(pstmt != null) try { pstmt.close(); } catch (Exception ex) { ex.printStackTrace(); }
            if(conn != null)  try { conn.close(); }  catch (Exception ex) { ex.printStackTrace(); }
        }
    }

    private StringBuffer addQuery(){
        Calendar calen = Calendar.getInstance();
        calen.add(Calendar.DATE, -1);
        Date ye1day = calen.getTime();

        String YESTERDAY = DateFormatUtils.format(ye1day, "yyyyMMdd");

        StringBuffer addQuery = new StringBuffer()
                               .append("         AND  CREATED < TO_DATE('" + TODAY + "000000', 'YYYYMMDDHH24MISS') \n")

                               //--Exist Image
                               //.append("   AND HP_MODEL IN (SELECT MODEL_NAME                                     \n")
                               //.append("                      FROM STC_G_CO_PH_SERVICE                            \n")
                               //.append("                     WHERE SITE_CD = '" + strCountry + "'                 \n")
                               //.append("                       AND PHONE_IMAGE IS NOT NULL)                       \n");

                               //--No Image
                               .append("   AND(   HP_MODEL NOT IN (SELECT MODEL_NAME                              \n")
                               .append("                             FROM STC_G_CO_PH_SERVICE                     \n")
                               .append("                            WHERE SITE_CD = '" + strCountry + "'          \n")
                               .append("                              AND PHONE_IMAGE IS NOT NULL)                \n")
                               .append("       OR HP_MODEL IS NULL)                                               \n");

        return addQuery;
    }
}
